#!/usr/bin/env python
#-*- coding: UTF-8 -*-

import pymysql
import time
import datetime
import uuid
import openpyxl
import os


class mysql_class:
    def __init__(self):
        self.host = '192.168.101.27'
        self.user = 'root'
        self.passwd = '123456'
        self.db = 'zhongmian'
        self.file_name = '提单信息导入模板.xlsx'

    def ljdb(self):
        self.conn = pymysql.connect(host=self.host,
                                    user=self.user,
                                    passwd=self.passwd,
                                    db=self.db,
                                    port=3306,
                                    charset='utf8')
        # conn = pymysql.connect(ip, username, pwd, schema,port)
        self.conn.ping(True)  #使用mysql ping来检查连接,实现超时自动重新连接
        # print(getCurrentTime(), u"MySQL DB Connect Success:",user+'@'+host+':'+str(port)+'/'+db)
        self.cur = self.conn.cursor()

    # 插入数据
    def insertData(self, table, my_dict):
        # print(my_dict)
        # print(my_dict['jj_code'])
        cols = ', '.join(my_dict.keys())
        # values = '","'.join(my_dict.values())
        values = '","'.join('%s' % v for v in my_dict.values())

        sql = "replace into %s (%s) values (%s)" % (table, cols,
                                                    '"' + values + '"')
        # print(sql)
        self.cur.execute(sql)
        self.conn.commit()

    # 查询数据
    def selectData(self, table):
        sql = "select * from " + table
        r = self.cur.execute(sql)  # 返回条数

        #fetchall：获取所有的数据，默认以元祖的方式返回，如果你指定了cursorclass = pymysql.cursors.DictCursor，则以dict的方式返回
        result = self.cur.fetchall()
        for row in result:
            print(row)

    # 获取execl数据
    def getExeclData(self):
        # 打开文件
        wb = openpyxl.load_workbook(self.file_name)
        # print(wb.get_sheet_names())
        # 获取所有的sheet
        sheet_names = wb.get_sheet_names()
        for sheet_name in sheet_names:
            # print(sheet_name)
            # print(wb.get_sheet_by_name(sheet_name))
            # print(wb[sheet_name])
            sh = wb[sheet_name]
            # 按行读取数据转化为列表
            rows_data = list(sh.rows)
            # print(rows_data)
            # 获取表单的表头信息
            titles = []
            for title in rows_data[0]:
                titles.append(title.value)
            # print(titles)
            for case in rows_data[1:]:
                # print(case)
                data = []
                for cell in case:  #获取一条测试用例数据
                    # print(cell.value)
                    data.append(cell.value)
                    # print(data)
                self.doDataCommon(sheet_name, titles, data)
                # print(data)

    # 共通的数据处理方法
    def doDataCommon(self, sheet_name, titles, data):
        # print(data)
        # print(len(titles))
        # print(len(data))

        result = {}
        result['id'] = str(uuid.uuid1())
        result['create_date'] = self.getCurrentTime()
        for i in range(len(titles)):
            result[titles[i]] = data[i]
        self.insertData(sheet_name, result)

    def getCurrentTime(self):
        # 获取当前时间
        return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))


if __name__ == "__main__":
    os.chdir(os.path.abspath(os.path.dirname(__file__)))

    starttime = datetime.datetime.now()
    print(starttime)

    mysql_class = mysql_class()
    mysql_class.ljdb()  # 连接MySQL
    # mysql_class.selectData('ZM_TBL_JNT_SEED_COTTON')
    mysql_class.getExeclData()

    endtime = datetime.datetime.now()
    print(endtime)

    print('\n数据处理成功!所用时间为：' + str((endtime - starttime).seconds))
